unit frmTanda;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ZDataset, DB, ZAbstractRODataset, ZAbstractDataset,
  ZAbstractTable, ZConnection, Setting, JvThread, JvThreadDialog,
  JvComponentBase;

type
  TForm5 = class(TForm)
    GroupBox1: TGroupBox;
    lblKabkot: TLabel;
    btnTanda: TButton;
    lbldesa: TLabel;
    ZConnection1: TZConnection;
    ZTable1: TZTable;
    ZQuery1: TZQuery;
    cbTanda: TComboBox;
    ZTable2: TZTable;
    Button1: TButton;
    Label1: TLabel;
    lblKec: TLabel;
    lblNama: TLabel;
    lblNIK: TLabel;
    txtAlasan: TEdit;
    JvThread2: TJvThread;
    JvThreadSimpleDialog2: TJvThreadSimpleDialog;
    procedure btnTandaClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button1Click(Sender: TObject);
    procedure cbTandaChange(Sender: TObject);
    procedure JvThread2FinishAll(Sender: TObject);
    procedure JvThread2Execute(Sender: TObject; Params: Pointer);
    function HandleSpec(Asal: String):String;
  private
    { Private declarations }
  public
    { Public declarations }
    constructor Create(AOwner: TComponent; tabel: String; ID:String; isganda:Boolean; IDT:String; bta:Integer;  pemil_list:TStringList; user:string=''; tabelx: String=''; IDX:String=''; IDTX:String='');             overload;
  end;

var
  Form5: TForm5;
  atb:Integer;
  ptabel,ptabelx,pid,pidx,tid,tidx,skec,sdesa:String;
  ganda:Boolean;
  username:string;
  pemilihs:TstringList;
  berhasil:boolean;
    tanggal, idtgl:string;
implementation

{$R *.dfm}
procedure TForm5.Button1Click(Sender: TObject);
begin
  Close;
end;

constructor TForm5.Create(AOwner: TComponent; tabel: String; ID:String; isganda:Boolean; IDT:String; bta:Integer; pemil_list:TStringList; user:string=''; tabelx: String=''; IDX:String=''; IDTX:String='');
begin
    ptabel:=tabel;
    pid:=ID;
    tid:=IDT;
    ganda:=isganda;
    atb:=bta;
    ptabelx:=tabelx;
    pidx:=IDX;
    tidx:=IDTX;
    username:=user;
    pemilihs:=pemil_list;
//    ShowMessage(ptabel+'-'+pid);
    inherited Create(AOwner);
end;

procedure TForm5.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TForm5.FormCreate(Sender: TObject);
var
  kabkot,kec,desa,nama,itabel,nik:String;
  i,idx:Integer;
  setting:TSetting_Form;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZTable2.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  BorderIcons := BorderIcons - [biMaximize];
  //kota
  ZTable1.Active:=false;
  ztable1.TableName:='kabkot';
  kabkot:=Copy(ptabel,9,2);
  ztable1.Filter:='KODE='+QuotedStr(kabkot);
  ztable1.Filtered:=true;
  ztable1.Active:=true;
  kabkot:=ztable1.FieldByName('TAMPIL').AsString;
  //kec
  ZTable1.Active:=false;
  ztable1.TableName:='kecamatan';
  kec:=Copy(ptabel,9,5);
  ztable1.Filter:='KODE_KECAMATAN='+QuotedStr(kec);
  ztable1.Filtered:=true;
  ztable1.Active:=true;
  kec:=ztable1.FieldByName('TAMPIL').AsString;
  //desa
  ZTable1.Active:=false;
  ztable1.TableName:='desa';
  desa:=Copy(ptabel,9,8);
  ztable1.Filter:='KODE_DESA='+QuotedStr(desa);
  ztable1.Filtered:=true;
  ztable1.Active:=true;
  desa:=ztable1.FieldByName('TAMPIL').AsString;
  //user
  {ZTable1.Active:=false;
  ztable1.TableName:=ptabel;
  ztable1.Filter:='ID_USER='+pid;
  ztable1.Filtered:=true;
  ztable1.Active:=true;
  nama:=ztable1.FieldByName('NAMA').AsString;
  nik:=ztable1.FieldByName('NIK').AsString;}

  lblKabkot.Caption:='Kota/Kabupaten: '+kabkot;
  lblKec.Caption:='Kecamatan: '+kec;
  lblDesa.Caption:='Kelurahan/Desa: '+desa;
  lblNama.Caption:=inttoStr(pemilihs.Count)+' pemilih akan dihapus';
  lblNIK.Caption:='';
  cbTanda.clear;
  cbtanda.Items.Add(' ');
  cbtanda.Items.Add('Meninggal Dunia'); //1
  cbtanda.Items.Add('Data Fiktif');
  cbtanda.Items.Add('TNI / POLRI');
  cbtanda.Items.Add('Pindah Alamat');
  cbtanda.Items.Add('Terdaftar Ganda');
  cbtanda.Items.Add('Lain-lain');           //5
  //if
  //if ganda=true then
    //cbtanda.Items.Add('Bukan Data Ganda');  //6
  //
  //KEC
  with ZTable1 do
  begin
    Active := False;
    TableName := 'kecamatan';
    Filter := 'KODE_KABKOT='+QuotedStr(Copy(ptabel,9,2));
    Filtered:=True;
    Active := True;
    First;
  end;
end;

Function cekRTRW(kata:String) : String;
var
  stemp:String;
begin
  sTemp:='';
  if (Length(kata)=1) then
    sTemp:='00'+kata
  else if (Length(kata)=2) then
    sTemp:='0'+kata
  else if (Length(kata)>3) then
    sTemp:=Copy(kata, Length(kata)-3, 3)
  else
    sTemp:=kata;
  Result := sTemp;
end;

procedure TForm5.btnTandaClick(Sender: TObject);
var
  query,itabel,ique,pidn,tidn,idx,tidx,pidx,ptabelx:String;
  temp:TStrings;
  selectedbutton,i : integer;
  ket : string;

begin
  //masukin tanggal dulu gan
  berhasil := true;
  tanggal := FormatDateTime('yyyy-mm-dd hh:mm:ss', Now());
  with ZQuery1 do  //masukkin nih gan
  begin
    Active:=False;
    sql.Clear;
    sql.Text := 'INSERT INTO tanggal (TANGGAL) VALUES ("'+tanggal+'")';
    ExecSQL;
  end;
  with ZQuery1 do  //masukkin nih gan
  begin
    Active:=False;
    sql.Clear;
    sql.Text := 'SELECT * FROM tanggal ORDER BY ID_TANGGAL DESC';
    Active:=True;
    idtgl := FieldByName('ID_TANGGAL').AsString;  //dapat ID_TGL
  end;
  ket := cbTanda.Items[cbTanda.ItemIndex];
  if(cbTanda.ItemIndex = 6) then
    ket := txtAlasan.Text;

  selectedButton := MessageDlg('Apakah Anda yakin akan menghapus data?',mtConfirmation , mbOKCancel, 0);
  if (selectedButton = mrOK) then begin

    //masukin thread aja
    JvThread2.ThreadDialog:=JvThreadSimpleDialog2;
    JvThread2.Execute(self);
    

  end;
end;

procedure TForm5.cbTandaChange(Sender: TObject);
begin
  if (cbtanda.ItemIndex=6) then
    txtAlasan.Enabled:=true
  else
    txtAlasan.Enabled:=false;
end;

procedure TForm5.JvThread2FinishAll(Sender: TObject);
begin
  if(berhasil) then begin
        ShowMessage(inttostr(pemilihs.Count)+' pemilih telah diubah tanda statusnya');
        Close;
      end
      else
        showmessage('Pilih tanda dahulu');
end;

function TForm5.HandleSpec(Asal: String):String;
var
  temp:string;
begin
  temp := StringReplace(asal, '''', '',[rfReplaceAll, rfIgnoreCase]);
  temp := StringReplace(temp, '\', '',[rfReplaceAll, rfIgnoreCase]);
  result := temp;
end;

procedure TForm5.JvThread2Execute(Sender: TObject; Params: Pointer);
var
  i:integer;
  query,itabel,ique,pidn,tidn,idx,tidx,pidx,ptabelx:String;
  temp:TStrings;
  ket : string;
begin
  //ini for
    for i:=0 to pemilihs.Count-1 do begin
      pid := pemilihs[i];
      //showmessage(pemilihs[i]);
      with Zquery1 do begin
        active:=false;
        sql.Clear;
        query:='SELECT * FROM '+ptabel+' WHERE ID_USER='+pid;
//      ShowMessage(query);
        sql.Text:=query;
        Active:=true;
        first;

        iQue:='INSERT INTO `history` (ID_USER, `NIK`, `NKK`, `NAMA`, `TEMPAT`, `TANGGAL`, '+
        '`IBU`, `AGAMA`, `KAWIN`, `KELAMIN`, `KELAINAN`, `ALAMAT`, `RW`, `RT`, `DUSUN`, `KET`, '+
        '`ID_TANGGAL`, TPS, STATUS1, STATUS2, GANDA, STATUS3, STATUSP, ALASANP'+
        ',CACAT,PENDIDIKAN,PEKERJAAN,STATUSR,WN,USERNAME, KET_AKSI) VALUES '+
        '('+QuotedStr(tid+'-'+FieldByName('ID_USER').AsString)+
        ','+QuotedStr(HandleSpec(FieldByName('NIK').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('NKK').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('NAMA').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('TEMPAT').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('TANGGAL').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('IBU').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('AGAMA').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('KAWIN').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('KELAMIN').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('KELAINAN').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('ALAMAT').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('RW').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('RT').AsString))+
        ','+QuotedStr(HandleSpec(FieldByName('DUSUN').AsString))+
        ','+QuotedStr(ket)+
        ','+FieldByName('ID_TANGGAL').AsString+
        ','+FieldByName('TPS').AsString+
        ','+QuotedStr(FieldByName('STATUS1').AsString)+
        ','+QuotedStr(FieldByName('STATUS2').AsString)+
        ','+QuotedStr(FieldByName('GANDA').AsString)+
        ','+QuotedStr(FieldByName('STATUS3').AsString)+
        ','+QuotedStr(FieldByName('STATUSP').AsString)+
        ','+QuotedStr(FieldByName('ALASANP').AsString)+
        ','+QuotedStr(FieldByName('CACAT').AsString)+
        ','+QuotedStr(FieldByName('PENDIDIKAN').AsString)+
        ','+QuotedStr(FieldByName('PEKERJAAN').AsString)+
        ','+QuotedStr(FieldByName('STATUSR').AsString)+
        ','+QuotedStr(FieldByName('WN').AsString)+
        ','+QuotedStr(username)+
        ','+QuotedStr('2')+
        ')';
        //
        active:=false;
        sql.Clear;
        //ShowMessage(ique);
        sql.Text:=ique;
        ExecSQL;
      end;
      if ((cbtanda.ItemIndex>0) and (cbtanda.ItemIndex<=4)) or (cbtanda.ItemIndex=6) then begin
      //ubah tanda biasa
        ZQuery1.Active:=false;
        zquery1.SQL.Clear;
        query:='UPDATE '+ptabel+' SET STATUS2='+QuotedStr(IntToStr(cbtanda.ItemIndex))+', ID_TANGGAL = '+idtgl+', TPS = 0 WHERE ID_USER='+pid;
        zquery1.SQL.text:=query;
        zquery1.ExecSQL;
        //Close;
      end
      else
      if (cbtanda.ItemIndex=5) then begin
        //hapus
        with ZQuery1 do begin
          //
          active:=false;
          sql.Clear;
          itabel:=ptabel+'-'+pid;
          query:='UPDATE '+ptabel+' SET STATUS2='+QuotedStr(IntToStr(5))+', ID_TANGGAL = '+idtgl+', TPS = 0 WHERE ID_USER='+pid;
          //ShowMessage(query);
          sql.Text:=query;
          ExecSQL;
        end;
        //cek data ganda
        with ZQuery1 do begin
          Active:=false;
          sql.Clear;
          query:='SELECT * FROM data_ganda WHERE ID_USER1='+QuotedStr(tid+'-'+pid)+' OR ID_USER2='+QuotedStr(tid+'-'+pid);
          SQL.Text:=query;
          Active:=true;
        end;
        //kalau cuma sepasang, ubah
        {if ZQuery1.RecordCount=1 then begin
          if (ZQuery1.FieldByName('ID_USER1').AsString=tid+'-'+pid) then
            idx:=ZQuery1.FieldByName('ID_USER2').AsString
          else
            idx:=ZQuery1.FieldByName('ID_USER1').AsString;
          with ZQuery1 do begin
            Active:=false;
            sql.Clear;
            query:='UPDATE '+ptabel+' SET GANDA="0", ID_TANGGAL = '+idtgl+' WHERE ID_USER='+pid;
            SQL.Text:=query;
            ExecSQL;
        //
            Active:=false;
            sql.Clear;
            //if atb=1 then
            query:='DELETE FROM data_ganda WHERE ID_USER1='+QuotedStr(tid+'-'+pid)+' OR ID_USER2='+QuotedStr(tid+'-'+pid);
            //else
            //  query:='DELETE FROM data_ganda WHERE (ID_USER1='+QuotedStr(tid+'-'+pid)+' AND ID_USER2='+QuotedStr(tidx+'-'+pidx)+') OR (ID_USER1='+QuotedStr(tidx+'-'+pidx)+' AND ID_USER2='+QuotedStr(tid+'-'+pid)+')';
            SQL.Text:=query;
            ExecSQL;
          end;
        end;
    /////
        with ZQuery1 do begin
          Active:=false;
          sql.Clear;
          query:='SELECT * FROM data_ganda WHERE ID_USER1='+QuotedStr(idx)+' OR ID_USER2='+QuotedStr(idx);
          SQL.Text:=query;
          Active:=true;
        end;
    //kalau sudah habis, ubah
        if (ZQuery1.RecordCount<1) and (idx<>'') then begin
          with ZQuery1 do begin
            Active:=false;
            sql.Clear;
            temp := TStringList.Create; //1
            try
              ExtractStrings(['-'], [], PChar(idx), temp);
              tidx := temp[0];
              pidx := temp[1];
            finally
              temp.Free;
            end;
            query:='SELECT * FROM penyimpan WHERE ID='+tidx;
            sql.Text:=query;
            active:=True;
            ptabelx:=FieldByName('NAMA').AsString;
            //
            active:=false;
            query:='SHOW TABLES LIKE '+quotedStr(ptabelx);
            SQL.Text:=query;
            active:=true;
            if(recordCount > 0) then begin
              Active:=false;
              sql.Clear;
              query:='UPDATE '+ptabelx+' SET GANDA="0", ID_TANGGAL = '+idtgl+' WHERE ID_USER='+pidx;
              SQL.Text:=query;
              ExecSQL;
            end;
          end;
        end;}
        //ShowMessage('Data telah dihapus');
        //Close;
      end
      else
      if (cbtanda.ItemIndex=7) then begin
        //ganda
        //cek data_ganda
        with ZQuery1 do begin
          Active:=false;
          sql.Clear;
          query:='SELECT * FROM data_ganda WHERE ID_USER1='+QuotedStr(tid+'-'+pid)+' OR ID_USER2='+QuotedStr(tid+'-'+pid);
          SQL.Text:=query;
          Active:=true;
        end;
        //kalau cuma sepasang, ubah
        if ZQuery1.RecordCount>=1 then begin
          if (ZQuery1.FieldByName('ID_USER1').AsString=tid+'-'+pid) then
            idx:=ZQuery1.FieldByName('ID_USER2').AsString
          else
            idx:=ZQuery1.FieldByName('ID_USER1').AsString;
          with ZQuery1 do begin
            Active:=false;
            sql.Clear;
            query:='UPDATE '+ptabel+' SET GANDA="0", ID_TANGGAL = '+idtgl+' WHERE ID_USER='+pid;
            SQL.Text:=query;
            ExecSQL;
          //
            Active:=false;
            sql.Clear;
            if atb=1 then
              query:='DELETE FROM data_ganda WHERE ID_USER1='+QuotedStr(tid+'-'+pid)+' OR ID_USER2='+QuotedStr(tid+'-'+pid)
            else
              query:='DELETE FROM data_ganda WHERE (ID_USER1='+QuotedStr(tid+'-'+pid)+' AND ID_USER2='+QuotedStr(tidx+'-'+pidx)+') OR (ID_USER1='+QuotedStr(tidx+'-'+pidx)+' AND ID_USER2='+QuotedStr(tid+'-'+pid)+')';
            SQL.Text:=query;
            ExecSQL;
          end;
        end;
        /////
        with ZQuery1 do begin
          Active:=false;
          sql.Clear;
          query:='SELECT * FROM data_ganda WHERE ID_USER1='+QuotedStr(idx)+' OR ID_USER2='+QuotedStr(idx);
          SQL.Text:=query;
          Active:=true;
        end;
        //kalau sudah habis, ubah
        if (ZQuery1.RecordCount<1) and (idx<>'') then begin
          with ZQuery1 do begin
            Active:=false;
            sql.Clear;
            temp := TStringList.Create; //1
            try
              ExtractStrings(['-'], [], PChar(idx), temp);
              tidx := temp[0];
              pidx := temp[1];
            finally
              temp.Free;
            end;
            query:='SELECT * FROM penyimpan WHERE ID='+tidx;
            sql.Text:=query;
            active:=True;
            ptabelx:=FieldByName('NAMA').AsString;
            //
            Active:=false;
            sql.Clear;
            query:='UPDATE '+ptabelx+' SET GANDA="0" WHERE ID_USER='+pidx;
            SQL.Text:=query;
            ExecSQL;
          end;
        end;
        //ShowMessage('Pemilih telah dihapus');
        //Close;
      end
      else
        berhasil := false;
//        showmessage('Pilih tanda dahulu');
    end;
end;

end.
